package com.xdap.materialselect.dao;

import com.alibaba.fastjson.JSON;
import com.definesys.mpaas.query.MpaasQuery;
import com.definesys.mpaas.query.MpaasQueryFactory;
import com.xdap.api.constant.ApplicationConstant;
import com.xdap.api.constant.TenantConstant;
import com.xdap.api.moudle.privateapaas.appmanage.pojo.ApaasApplications;
import com.xdap.api.moudle.roles.pojo.entity.ApaasRoleUserParams;
import com.xdap.api.moudle.roles.pojo.entity.XdapRoleUsers;
import com.xdap.api.moudle.roles.pojo.entity.XdapRoles;
import com.xdap.api.moudle.tenant.pojo.XdapTenantAdmins;
import com.xdap.api.moudle.user.pojo.XdapUsers;
import com.xdap.api.moudle.watercode.pojo.XdapWaterCodeStream;
import com.xdap.materialselect.entity.dto.ProjectQueryDto;
import com.xdap.materialselect.entity.dto.ReportQueryDto;
import com.xdap.materialselect.entity.dto.TestItemHeaderDto;
import com.xdap.materialselect.entity.po.*;
import com.xdap.materialselect.entity.vo.ReportDataVo;
import com.xdap.materialselect.util.MaSelectCommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description:
 * @Auther:huhanrui
 * @Date:2025/7/16 14:26
 */
@Slf4j
@Component
public class MaterialSelectDao {

    @Autowired
    private MpaasQueryFactory sw;



    public TmaterialSelectionCustomer getMaterialSelectById(String id) {
        return this.sw.buildQuery()
                .eq("id", id)
                .doQueryFirst(TmaterialSelectionCustomer.class);
    }

    public List<TestItemHeaderDto> getProjectByTabId(String tabDocId) {
        return this.sw.buildQuery()
                .sql("select tti.id as projectId,tti.test_item_stati_type as projectType,tti.test_item_name as projectName,tci.t_lsl,tci.t_usl,tci.t_seq as sortOrder \n" +
                        "from t_control_info tci  \n" +
                        "left join t_test_item tti on tci.t_project = concat('[\"',tti.id,'\"]')\n" +
                        "where tci.tab_doc_id  = #tabDocId")
                .setVar("tabDocId", tabDocId)
                .doQuery(TestItemHeaderDto.class);
    }

    public MpaasQuery buildReportSelect(StringBuilder sql, ReportQueryDto reportQueryDto) {

        Map<String, Object> queryMap = new HashMap<>();
        sql.append(" from t_batch_common_info tbci  -- 库存分类-批次纬度 \n" +
                "left join t_mat_test tmt on tbci.batch_no  = tmt.batch  -- 检测数据 \n" +
                "left join t_test_item tti on tmt.inspection_id = CONCAT('[\"',tti.id,'\"]') and tti.is_enable = 'TRUE' -- 检测项目(启用) \n" +
                "left join t_unpass_tag tut on tbci.static_tag = CONCAT('[\"',tut.id,'\"]')  -- 分类标签 \n" +
                "left join t_product_type tpt on tbci.material_model  = CONCAT('[\"',tpt.id,'\"]') -- 物料型号\n" +
                "left join t_standard_judgment tsj on tsj.id is null -- 客户标准项判定表\n" +
                "left join t_standard_judgment_end tsje  on tsje.id is null -- 客户标准判定结果表\n" +
                "where 1=1 \n");
        //挑料单（根据挑料单 单选）
        String maSelectId = reportQueryDto.getMaterialSelectId();
        if (StringUtils.isNotBlank(maSelectId)) {
            //查询：【客户挑料单】
            TmaterialSelectionCustomer tmaterialSelectionCustomer = this.getMaterialSelectById(maSelectId);
            String uniqueIdentifier = tmaterialSelectionCustomer.getUniqueIdentifier();
            String temp = sql.toString().replaceFirst("left join t_standard_judgment tsj on tsj.id is null -- 客户标准项判定表\n" +
                            "left join t_standard_judgment_end tsje  on tsje.id is null -- 客户标准判定结果表\n" +
                            "where 1=1 \n",
                    "left join t_standard_judgment tsj on tmt.batch  = tsj.t_batch_no and tsj.t_test_id = tmt.id and tsj.unique_identifier = '" + uniqueIdentifier + "' -- 客户标准项判定表\n" +
                            "left join t_standard_judgment_end tsje  on tsje.t_batch_no = tmt.batch and tsje.unique_identifier = '" + uniqueIdentifier + "' -- 客户标准判定结果表\n" +
                            "where 1=1 \n");
            sql = new StringBuilder(temp);
            //根据基地，产线，物料类型 查询【挑料报表数据参数】
            String productionBase = tmaterialSelectionCustomer.getProductionBase();//数据单选
            String materialModel = tmaterialSelectionCustomer.getMaterialModel();//数据选择-多选
            String baseMaSelect = MaSelectCommonUtil.insertBracket(productionBase);//数据选择
            List<String> modelListMaSelect = JSON.parseArray(materialModel, String.class);
            modelListMaSelect = modelListMaSelect.stream().map(m -> MaSelectCommonUtil.insertBracket(m)).collect(Collectors.toList());
            sql.append("and tbci.production_base = #baseMaSelect \n" +
                    "and tbci.material_model in (#modelListMaSelect) \n");
            queryMap.put("baseMaSelect", baseMaSelect);
            queryMap.put("modelListMaSelect", modelListMaSelect);

            //系统判定结果
            if (CollectionUtils.isNotEmpty(reportQueryDto.getCheckResult())) {
                List<String> resultQuery = reportQueryDto.getCheckResult();
                sql.append("and tsje.t_result in (#resultQuery) \n");
                queryMap.put("resultQuery", resultQuery);
            }
        }
        //基地筛选-多选
        if (CollectionUtils.isNotEmpty(reportQueryDto.getBase())) {
            List<String> baseId = reportQueryDto.getBase();
            List<String> baseQuery = baseId.stream().map(m -> MaSelectCommonUtil.insertBracket(m)).collect(Collectors.toList());
            sql.append("and tbci.production_base in ( #baseQuery ) \n");
            queryMap.put("baseQuery", baseQuery);
        }
        //产品类型-单选 e.g. = ["PRODUCT"]
        if (StringUtils.isNotBlank(reportQueryDto.getProductType())) {
            String productTypeQuery = MaSelectCommonUtil.insertBracket(reportQueryDto.getProductType());
            sql.append("and tbci.product_type = #productType \n");
            queryMap.put("productType", productTypeQuery);
        }
        //规格型号型号（单选）
        if (StringUtils.isNotBlank(reportQueryDto.getMaterialModel())) {
            String modelQuery = MaSelectCommonUtil.insertBracket(reportQueryDto.getMaterialModel());
            sql.append("and tbci.material_model = #materialModel \n");
            queryMap.put("materialModel", modelQuery);
        }
        //分类标签
        if (StringUtils.isNotBlank(reportQueryDto.getTag())) {
            String tagQuery = MaSelectCommonUtil.insertBracket(reportQueryDto.getTag());
            sql.append("and tbci.static_tag = #tagQuery \n");
            queryMap.put("tagQuery", tagQuery);
        }

        //在库状态,默认在库
        if (CollectionUtils.isNotEmpty(reportQueryDto.getPickStatus())) {
            String pickStatusQuery = reportQueryDto.getPickStatus().stream().map(m -> MaSelectCommonUtil.insertBracket(m)).collect(Collectors.joining(","));
            sql.append("and tbci.pick_status in ( #pickStatusQuery ) \n");
            queryMap.put("pickStatusQuery", pickStatusQuery);
        }else{
            sql.append("and tbci.pick_status = '[\"IN\"]'  \n");
        }
        //入库日期范围
        if(StringUtils.isNotBlank(reportQueryDto.getStorageStartDate()) && StringUtils.isNotBlank(reportQueryDto.getStorageEndDate())){
            sql.append("and DATE_FORMAT(storage_date,'%Y-%m-%d') BETWEEN  #storageStartDate and #storageEndDate \n");
            queryMap.put("storageStartDate", reportQueryDto.getStorageStartDate());
            queryMap.put("storageEndDate", reportQueryDto.getStorageEndDate());
        }
        //备注模糊查询
        if (StringUtils.isNotBlank(reportQueryDto.getRemark())) {
            sql.append("and tbci.notes like '%").append(reportQueryDto.getRemark()).append("%' \n");
        }
        log.info("挑料工作台报表SQL:{}", sql);
        MpaasQuery mpaasQuery = this.sw.buildQuery().sql(sql.toString());
        for (String key : queryMap.keySet()) {
            mpaasQuery.setVar(key, queryMap.get(key));
        }
        return mpaasQuery;
    }

    public List<TestItemHeaderDto> getSelectReportHeader(ReportQueryDto reportQueryDto) {
        StringBuilder sql = new StringBuilder();
        sql.append("select  distinct tti.id as projectId,tti.test_item_stati_type as projectType,tti.test_item_name as projectName,tmt.test_item_seq as sortOrder \n" );
        MpaasQuery mpaasQuery = buildReportSelect(sql, reportQueryDto);
        List<TestItemHeaderDto> testItemHeaderDtoList = mpaasQuery.doQuery(TestItemHeaderDto.class);
        return testItemHeaderDtoList;
    }

    public List<ReportDataVo> getSelectReportDate(ReportQueryDto reportQueryDto) {
        StringBuilder sql = new StringBuilder();
        sql.append("select \n" +
                "tbci.batch_no as batch,tpt.t_name as materialModel,\n" +
                "tut.tag_name,tbci.current_weight_kg,tbci.document_id,tbci.pick_status,tbci.seq, \n" +
                "tbci.balance_weight,\n" +
                "tsje.t_result,\n" +
                "tbci.storage_date,tbci.out_date,tbci.notes,\n" +
                "tsj.upper_deviation,tsj.lower_deviation,tsj.t_result as cellResult,\n" +
                "tmt.customer_code ,tmt.modify_time,tmt.test_item_seq,\n" +
                "tti.id as projectId,tti.test_item_stati_type as projectType,tti.test_item_name as projectName,tmt.value  \n" );
        MpaasQuery mpaasQuery = buildReportSelect(sql, reportQueryDto);
        List<ReportDataVo> reportDataVoList = mpaasQuery.doQuery(ReportDataVo.class);
        return reportDataVoList;
    }


    public  List<TmatInfo> getPackByNos(List<String> batchNoList){
        return this.sw.buildQuery()
                .in("batch_no", batchNoList)
                .doQuery(TmatInfo.class);
    }
    public void updatePackStaticTag( List<String> ids, String staticTag ){
        this.sw.buildQuery()
                .in("id", ids)
                .update("static_tag", staticTag)
                .doUpdate(TmatInfo.class);
    }

    public  List<TbatchCommonInfo> getBatchByNos( List<String> batchNoList ){
        return this.sw.buildQuery()
                .in("batch_no", batchNoList)
                .doQuery(TbatchCommonInfo.class);
    }


    public void updatebatchStaticTag( List<String> ids, String staticTag ){
        this.sw.buildQuery()
                .in("id", ids)
                .update("static_tag", staticTag)
                .doUpdate(TbatchCommonInfo.class);
    }

    public  List<TmatInfo> getPackForHandle(List<String> batchNoList){
        return this.sw.buildQuery()
                .in("batch_no", batchNoList)
                .eq("pick_status", "[\"IN\"]")
                .doQuery(TmatInfo.class);
    }
    public  List<TbatchCommonInfo> getBatchForHandle(List<String> batchNoList){
        return this.sw.buildQuery()
                .in("batch_no", batchNoList)
                .doQuery(TbatchCommonInfo.class);
    }

    public void insertGcsBatchHanleList(List<GcsBatchHanle> gcsBatchHanleList){
        this.sw.buildQuery()
                .doBatchInsert(gcsBatchHanleList);
    }

    public void updateBatchWeight(TbatchCommonInfo tbatchCommonInfo){
        this.sw.buildQuery()
                .eq("id", tbatchCommonInfo.getId())
                .update("current_weight_kg", tbatchCommonInfo.getCurrentWeightKg())
                .update("lock_weight_kg", tbatchCommonInfo.getLockWeightKg())
                .doUpdate(TbatchCommonInfo.class);
    }
    public void updatePackWeight(TmatInfo tmatInfo){
        this.sw.buildQuery()
                .eq("id", tmatInfo.getId())
                .update("disposal_method", tmatInfo.getDisposalMethod())
                .update("pack_handle", tmatInfo.getPackHandle())
                .update("disposal_suggestion", tmatInfo.getDisposalSuggestion())
                .update("lock_weight_kg", tmatInfo.getLockWeightKg())
                .update("current_weight_kg", tmatInfo.getCurrentWeightKg())
                .update("handle_tag", tmatInfo.getHandleTag())
                .update("pick_status", tmatInfo.getPickStatus())
                .doUpdate(TmatInfo.class);
    }

    //xdap_watercode_stream
    public XdapWaterCodeStream getWaterValue(String waterRuleId,String componentId){
        return  this.sw.buildQuery()
                .eq("water_rule_id", waterRuleId)
                .eq("component_id", componentId)
                .doQueryFirst(XdapWaterCodeStream.class);
    }


    public void addCodeValue(XdapWaterCodeStream waterValue){
        this.sw.buildQuery()
                .eq("id", waterValue.getId())
                .update("code_value", waterValue.getCodeValue())
                .doUpdate(XdapWaterCodeStream.class);
    }


    public  List<TtestItem> getProjectByIds( List<String> ids){
        return this.sw.buildQuery()
                .in("id", ids)
                .doQuery(TtestItem.class);
    }

    public  List<ApaasRoleUserParams>   queryRolePowerBaseList(String appId,String userId){
        return this.sw.buildQuery()
                .sql("SELECT  arup.* from xdap_roles xr \n" +
                        "left join apaas_role_params arp on arp.param_code  = 'mf_org' and arp.role_id  = xr.id\n" +
                        "left join apaas_role_user_params arup on arup.role_id  = xr.id and arup.role_param_id  = arp.id\n" +
                        "where xr.app_id  = #appId \n" +
                        "and xr.enable_group_param  = #enableGroupParam \n" +
                        "and arup.user_id  = #userId ")
                .setVar("enableGroupParam", "ENABLE")
                .setVar("appId", appId)
                .setVar("userId", userId)
                .doQuery(ApaasRoleUserParams.class);
    }

    public List<Tbase> queryPowerBaseList( List<String> powerBaseList){
        return this.sw.buildQuery()
                .in("t_name", powerBaseList)
                .doQuery(Tbase.class);
    }

    public  List<TmaterialSelectionCustomer> queryPowerSelectOrder(String userId){
        return this.sw.buildQuery()
                .sql("SELECT * from t_material_selection_customer\n" +
                        "where created_by  in (\n" +
                        "SELECT xdu2.user_id  from xdap_dept_users xdu \n" +
                        "left join xdap_departments xd on  xd.status  = 'ENABLE' and xd.structure_code LIKE CONCAT('%',xdu.department_id,'%') \n" +
                        "left join xdap_dept_users xdu2 on xdu2.department_id  = xd.id \n" +
                        "where xdu.user_id  = #userId );")
                .setVar("userId",userId)
                .doQuery(TmaterialSelectionCustomer.class);
    }
}
